<?php
class Marticle extends CI_Model{
	protected $_table="tpt_article";
    protected $_table_1="tpt_article_content";
	protected $_table_2="tpt_catalogue";
	protected $_table_3="tpt_section";
    protected $_table_4="tpt_language";
    protected $_table_5="tpt_sub_article";
	public function __construct(){
		parent::__construct();	
		$this->load->database();
	} 	

	public function listArticle(){
        $sSortDir = $this->input->get_post('sSortDir_0', true);
        
        /* Array of database columns which should be read and sent back to DataTables. Use a space where
         * you want to insert a non-database field (for example a counter or static image)
         */
        $sTable = $this->_table;
        $sTable_1 = $this->_table_1;
        $sTable_2 = $this->_table_2;
        $sTable_3 = $this->_table_3;

        $aColumns = array("{$sTable}.art_id","{$sTable_3}.sec_name","{$sTable_2}.cata_name","{$sTable_1}.art_title","{$sTable}.art_author","{$sTable}.art_check","{$sTable}.date_add");
        
        $aColumns2 = array('sec_name','cata_name','art_id','art_title','art_author','art_check','date_add');
        
        // DB table to use
        $this->db->join($sTable_1,$sTable.'.art_id = '.$sTable_1.'.art_id','left');
        $this->db->join($sTable_2,$sTable.'.cata_id = '.$sTable_2.'.cata_id','left');
        $this->db->join($sTable_3,$sTable.'.sec_id = '.$sTable_3.'.sec_id','left');

        $this->db->where($sTable_1.'.lang_id',1);

        $this->db->order_by("{$sTable}.art_id","DESC");

        $iDisplayStart = $this->input->get_post('iDisplayStart', true);
        $iDisplayLength = $this->input->get_post('iDisplayLength', true);
        $iSortCol_0 = $this->input->get_post('iSortCol_0', true);
        $iSortingCols = $this->input->get_post('iSortingCols', true);
        $sSearch = $this->input->get_post('sSearch', true);
        $sEcho = $this->input->get_post('sEcho', true);
        $myFilter = $this->input->get_post('myFilter', true);
    
        // Paging
        if(isset($iDisplayStart) && $iDisplayLength != '-1')
        {
            $this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart));
        }
        
        // Ordering
        if(isset($iSortCol_0))
        {
            for($i=0; $i<intval($iSortingCols); $i++)
            {
                $iSortCol = $this->input->get_post('iSortCol_'.$i, true);
                $bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
                $sSortDir = $this->input->get_post('sSortDir_'.$i, true);
    
                if($bSortable == 'true')
                {
                    $this->db->order_by($aColumns[intval($this->db->escape_str($iSortCol)) + 1], $this->db->escape_str($sSortDir));
                }
            }
        }
        
        /* 
         * Filtering
         * NOTE this does not match the built-in DataTables filtering which does it
         * word by word on any field. It's possible to do here, but concerned about efficiency
         * on very large tables, and MySQL's regex functionality is very limited
         */
        if(isset($sSearch) && !empty($sSearch))
        {
            for($i=0; $i<count($aColumns); $i++)
            {
                $bSearchable = $this->input->get_post('bSearchable_'.$i, true);
                
                // Individual column filtering
                if(isset($bSearchable) && $bSearchable == 'true')
                {
                    $this->db->or_like($aColumns[$i], $this->db->escape_like_str($sSearch));
                }
            }
        }

        // my filter
        if(isset($myFilter) && !empty($myFilter)){
            $myFilter = json_decode($myFilter);
            foreach($myFilter as $filter => $keyword){
                if($keyword != 'all'){
                    $this->db->where($filter, $keyword);
                }
            }
        }
        
        // Select Data
        $this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);
        $rResult = $this->db->get($sTable);
    
        // Data set length after filtering
        $this->db->select('FOUND_ROWS() AS found_rows');
        $iFilteredTotal = $this->db->get()->row()->found_rows;
    
        // Total data set length
        $iTotal = $this->db->count_all($sTable);
    
        // Output
        $output = array(
            'sEcho' => intval($sEcho),
            'iTotalRecords' => $iTotal,
            'iTotalDisplayRecords' => $iFilteredTotal,
            'aaData' => array()
        );
        
        foreach($rResult->result_array() as $aRow)
        {
            $row = array();
            
            foreach($aColumns2 as $col)
            {
                if(!in_array($col, array('art_id'))){
                    $value = '';
                    $value = $aRow[$col];
                    if($col == 'art_check'){
                    	if($value == 1){
                    		$value = '<a href="javascript:void(0)" class="btn btn-default tip active" art_id="'.$aRow['art_id'].'"><i class="icon16 i-checkmark"></a>';
                    	}else{
                    		$value = '<a href="javascript:void(0)" class="btn btn-default tip active" art_id="'.$aRow['art_id'].'"><i class="icon16 i-close"></a>';
                    	}
                    }
                    $row[] = $value;
                }
            }
            $row[] = '  <a href="' . base_url() . 'admincp/article/edit/id/' . $aRow['art_id'] . '" class="btn btn-default tip" title="Edit"><i class="icon16 i-pencil"></i></a>
                        <a href="javascript:void(0)" onclick="confirm_delete('.$aRow['art_id'].',\''.$this->router->fetch_class().'\');" class="btn btn-default tip" title="Delete"><i class="icon16 i-remove"></a>';
            $output['aaData'][] = $row;
            $output['my_id'][] = $aRow['art_id'];
        }
        return $output;
    }
	
	public function insert($data){
		$this->db->insert($this->_table,$data);	
	}

    public function list_all(){
        $lang = $this->session->userdata['current_language'];
        $this->db->select('b.art_title, b.art_id');
        $this->db->from($this->_table.' as a');
        $this->db->from($this->_table_1.' as b','a.art_id = b.art_id','left');
        $this->db->join($this->_table_2.' as c','a.cata_id = c.cata_id','left');
        $this->db->join($this->_table_3.' as d','a.sec_id = d.sec_id','left');
        $this->db->join($this->_table_4.' as e','b.lang_id = e.id','left');
        $this->db->where('a.art_check',1);
        $this->db->where('a.sec_id',2);
        $this->db->where('a.cata_id',1);
        $this->db->where('e.lang_code',$lang['code']);
        $this->db->order_by('a.art_id','desc');
        return $this->db->get()->result_array(); 
    }


	public function get_data($id){
        $dataLang = $this->registry->get('language');
        $maxLang = count($dataLang);
        $selectBeforeFrom = '';
        $selectAfterFrom = '';
        $where = 'b.art_id = '.$id;
        $i=1;
        foreach($dataLang as $lang){
            $i_next = $i + 1;
            if($i == 1){
                $selectBeforeFrom .= 'a1.art_id, b.sec_id, b.cata_id, b.tag_id, a1.art_title as art_title_'.$lang['lang_code'].', a1.art_content as art_content_'.$lang['lang_code'];
                $selectAfterFrom .= '(select * from tpt_article) b ,';
                $selectAfterFrom .= '(select * from tpt_article_content where lang_id = "'.$lang['id'].'") a1';
            }else{
                $selectBeforeFrom .= ', a'.$i.'.art_title as art_title_'.$lang['lang_code'].', a'.$i.'.art_content as art_content_'.$lang['lang_code'];    
                $selectAfterFrom .= ', (select * from tpt_article_content where lang_id = "'.$lang['id'].'") a'.$i;
                if($i == $maxLang){
                    $i_next = 1;
                }
            }
            $where .= ' AND a'.$i.'.art_id = b.art_id';
            $i++;
        }
        $sql = "select SQL_CALC_FOUND_ROWS ".$selectBeforeFrom." from ".$selectAfterFrom."
where ".$where." ORDER BY a1.art_id DESC";
        return $this->db->query($sql)->row_array();
	}

    public function get_sub_data($id, $lang){
        $this->db->where('art_id',$id);
        $this->db->where('lang_id',$lang);
        return $this->db->get($this->_table_5)->result_array();
    }

	public function delete($id){
		$this->db->where("art_id",$id);
		$this->db->delete($this->_table);

        $this->db->where("art_id",$id);
        $this->db->delete($this->_table_1);

        $this->db->where("art_id",$id);
        $this->db->delete($this->_table_5);
	}

	public function update($data,$id){
		$this->db->where("art_id",$id);
		$this->db->update($this->_table,$data);
	}
	
	public function update_view($url){
		$this->db->set('art_view', 'art_view + 1', FALSE);
		$this->db->where("art_title_url",$url);
		$this->db->update($this->_table);
	}

    public function get_article_study(){
        $this->db->select();
        $this->db->from($this->_table.' as a');
        $this->db->join($this->_table_2.' as b','a.cata_id = b.cata_id','left');
        $this->db->join($this->_table_3.' as c','a.sec_id = c.sec_id','left');
        $this->db->where("a.sec_id",2);
        $this->db->where("a.art_check",1);
        $this->db->limit(5);
        $this->db->order_by('a.art_id','desc');
        return $this->db->get()->result_array();
    }

    public function get_article_news(){
        $this->db->select();
        $this->db->from($this->_table.' as a');
        $this->db->join($this->_table_2.' as b','a.cata_id = b.cata_id','left');
        $this->db->join($this->_table_3.' as c','a.sec_id = c.sec_id','left');
        $this->db->where("a.sec_id",1);
        $this->db->where("a.art_check",1);
        $this->db->limit(5);
        $this->db->order_by('a.art_id','desc');
        return $this->db->get()->result_array();
    }

    public function get_article_relax(){
        $this->db->select();
        $this->db->from($this->_table.' as a');
        $this->db->join($this->_table_2.' as b','a.cata_id = b.cata_id','left');
        $this->db->join($this->_table_3.' as c','a.sec_id = c.sec_id','left');
        $this->db->where("a.sec_id",3);
        $this->db->where("a.art_check",1);
        $this->db->limit(5);
        $this->db->order_by('a.art_id','desc');
        return $this->db->get()->result_array();
    }

    public function get_name($url){
        $this->db->select('art_title');
        $this->db->where('art_title_url',$url); 
        return $this->db->get($this->_table)->row_array();
    }

    public function list_all_by_section($url){
        $this->db->select('a.art_title, a.art_title_url, a.art_info, a.art_content, a.date_add, a.art_img, b.cata_name, b.cata_name_url, c.sec_name, c.sec_name_url');
        $this->db->from($this->_table.' as a');
        $this->db->join($this->_table_2.' as b','a.cata_id = b.cata_id','left');
        $this->db->join($this->_table_3.' as c','a.sec_id = c.sec_id','left');
        $this->db->where('sec_name_url',$url);
		$this->db->order_by('a.art_id','desc');
        return $this->db->get()->result_array();
    }

    public function list_all_by_catalogue($sec_url,$cata_url){
        $this->db->select('a.art_title, a.art_title_url, a.art_info, a.art_content, a.date_add, a.art_img, b.cata_name, b.cata_name_url, c.sec_name, c.sec_name_url');
        $this->db->from($this->_table.' as a');
        $this->db->join($this->_table_2.' as b','a.cata_id = b.cata_id','left');
        $this->db->join($this->_table_3.' as c','a.sec_id = c.sec_id','left');
        $this->db->where('c.sec_name_url',$sec_url);
        $this->db->where('b.cata_name_url',$cata_url);
		$this->db->order_by('a.art_id','desc');
        return $this->db->get()->result_array();
    }
    
    public function detail($url){
        $this->db->select();
        $this->db->from($this->_table.' as a');
        $this->db->join($this->_table_2.' as b','a.cata_id = b.cata_id','left');
        $this->db->where("a.art_title_url",$url);
        return $this->db->get()->row_array();
    }	
}